C:/Projekte/RAIS/dataaccesslayer/Localisation.cs

Go to the documentation of this file.

using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Transactions;
using System.Text;
using RAIS.Common.Localization;
using Cultures = System.Collections.Generic.Dictionary<string, RAIS.Common.Localization.Culture>;
using TranslationItems = System.Collections.Generic.Dictionary<string, RAIS.Common.Localization.TranslationItem>;
using Contexts = System.Collections.Generic.Dictionary<string, RAIS.Common.Localization.Context>;

namespace RAIS.DataAccessLayer
{
    public class Localisation
    {
        #region Public static properties

                public static Cultures SupportedLanguages
                {
                        get
                        {
                                return GetSupportedLanguages();
                        }
                }
                public static Contexts Contexts
                {
                        get
                        {
                                return GetContexts();
                        }
                }
                #endregion
        #region Public static methods

        public static string Translate(string context, string textToTranslate, string sourceLanguage, string targetLanguage)
        {

            if (string.IsNullOrEmpty(textToTranslate) || (textToTranslate == "&nbsp|&nbsp")) // || sourceLanguage == targetLanguage )
            {
                return textToTranslate;
            }
            else
            {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetTranslation, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Context, context);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TextToTranslate, textToTranslate);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SourceLanguage, sourceLanguage);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TargetLanguage, targetLanguage);

                                                using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                                {
                                                        sqlReader.Read();

                                                        if (sqlReader.HasRows)
                                                                return sqlReader["TranslatedText"].ToString();
                                                        else
                                                                throw new Exception("(" + textToTranslate + ") " + "cannot be translated into " + "(" + targetLanguage + ")");
                                                }
                                        }
                                }
            }
        }
        public static bool CultureExists( String cultureName )
        {
            Cultures cultures = GetSupportedLanguages();
            return cultures.ContainsKey(cultureName);
        }
        public static bool AddCulture(RAIS.Common.Localization.Culture culture, ref string returnMessage)
        {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddSupportedLanguage, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SupportedLanguageID, culture.LCID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SupportedLanguageName, culture.Name);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SupportedLanguageEnglishName, culture.EnglishName);
                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                returnMessage = RAIS.Common.Messages.CULTURE_ADDED;
                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
        public static bool SaveCulture(RAIS.Common.Localization.Culture culture, ref string returnMessage)
        {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateSupportedLanguage, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SupportedLanguageID, culture.LCID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RightToLeft, culture.RightToLeft);
                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                returnMessage = RAIS.Common.Messages.CULTURE_SAVED;
                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
        public static bool DeleteCulture(RAIS.Common.Localization.Culture culture, ref string returnMessage)
        {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_DeleteSupportedLanguage, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SupportedLanguageID, culture.ID);
                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                returnMessage = RAIS.Common.Messages.CULTURE_DELETED;
                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static TranslationItems GetAllTranslations(Culture sourceLanguage, Culture targetLanguage, bool untranslated, string sortExpression, string sortDirection)
                {
                        return GetTranslations(null, sourceLanguage, targetLanguage, untranslated, sortExpression, sortDirection);
                }
                public static TranslationItems GetTranslationsByContext(Context context, Culture sourceLanguage, Culture targetLanguage, bool untranslated, string sortExpression, string sortDirection)
                {
                        return GetTranslations(context, sourceLanguage, targetLanguage, untranslated, sortExpression, sortDirection);
                }
                public static bool SaveTranslations(TranslationItems translations, ref string returnMessage)
                {
                        bool returnCode = true;
            using (TransactionScope ts = new TransactionScope())
            {
                int count = 0;
                foreach (KeyValuePair<string, TranslationItem> translation in translations)
                {
                    if (translation.Value.Modified)
                    {
                        returnCode &= SaveTranslation(translation.Value, ref returnMessage);

                        if (!returnCode)
                            break;
                        else
                            count++;
                    }
                }

                if (returnCode)
                {
                    if( count == 1 )
                        returnMessage = string.Format(RAIS.Common.Messages.SAVED_TRANSLATION, count);
                    else
                        returnMessage = string.Format(RAIS.Common.Messages.SAVED_TRANSLATIONS, count);
                    ts.Complete();
                }
            }
                        return returnCode;
                }
                #endregion
        #region Private static helper methods

                static Cultures GetSupportedLanguages()
                {
                        Cultures supportedLanguages = new Cultures();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetSupportedLanguages, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        Culture culture = new Culture(sqlReader);
                                                        supportedLanguages.Add(culture.Name, culture);
                                                }
                                        }
                                }
                        }
                        return supportedLanguages;
                }
                static Contexts GetContexts()
                {
                        Contexts contexts = new Contexts();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetContexts, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        Context context = new Context(sqlReader);
                                                        contexts.Add(context.Name, context);
                                                }
                                        }
                                }
                        }
                        return contexts;
                }
                static TranslationItems GetTranslations(Context context, Culture sourceLanguage, Culture targetLanguage, bool untranslatedOnly, string sortExpression, string sortDirection)
                {
                        TranslationItems translationItems = new TranslationItems();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetTranslations, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                        if (context != null)
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_ContextID, context.ID);
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SourceLanguage, sourceLanguage.EnglishName);
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TargetLanguage, targetLanguage.EnglishName);
                                        if (untranslatedOnly)
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Untranslated, untranslatedOnly);
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SortColumn, sortExpression);
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SortDirection, sortDirection);

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        TranslationItem translationItem = new TranslationItem(sqlReader, sourceLanguage, targetLanguage);
                            if (!translationItems.ContainsKey(translationItem.Name))
                                                            translationItems.Add(translationItem.Name, translationItem);
                                                }
                                        }
                                }
                        }

                        return translationItems;
                }
                static bool SaveTranslation(TranslationItem translation, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateTranslation, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TranslationID, translation.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TargetLanguage, translation.TargetLanguage.EnglishName);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TranslatedText, translation.TargetText);
                                                sqlCommand.ExecuteNonQuery();

                                                translation.Modified = false;
                                        }
                                }
                                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                #endregion
    }
}